﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using jsm.SqlHelper;

namespace dbcaac
{
    public sealed class EntityJiyuanXMSH : EntityBase
    {
        public string xmrzLogId { get; private set; }
        public string jyxmId { get; private set; }
        public bool option { get; private set; }

        public string pageSize { get; private set; }
        public string pageNumber { get; private set; }

        public override void loadPageData(HttpContext context)
        {
            jyxmId = context.Request["id"];
            xmrzLogId = context.Request["logid"];
            if (null != context.Request["option"])
            {
                option = bool.Parse(context.Request["option"]);
            }
            pageSize = context.Request["rows"];      // 每页的行数
            pageNumber = context.Request["page"];    // 当前页码
        }

        public override int getTotal()
        {
            string sql = "SELECT count(*) FROM [jiyuanXM] WHERE jyxmDeleteFlag <> 1";

            return (int)SqlServerHelper.ExecuteScalar(sql);
        }

        protected override int doInsertOperation()
        {
            using (SqlServerHelper helper = new SqlServerHelper())
            {
                helper.Open();
                SqlTransaction transaction = helper.Connection.BeginTransaction();
                helper.Command.Transaction = transaction;
                try
                {
                    int retVal = 0;
                    if (option) // 审核通过
                    {
                        // 1、插入数据（修改标志位，使数据有效）
                        helper.Command.CommandText = @"UPDATE [jiyuanXM] SET jyxmNewFlag = 0 WHERE jyxmId = @jyxmId ";
                        helper.AddParameter("@jyxmId", SqlDbType.Int, int.Parse(jyxmId));
                        retVal = helper.ExecuteNoneQuery();

                        // 2、记录日志
                        helper.Command.CommandText = @"UPDATE [jiyuanXMLog] SET xmrzApprovalStatus = @xmrzApprovalStatus, xmrzApprovalDate = @xmrzApprovalDate,
                                                   xmrzApprovalBy = @xmrzApprovalBy WHERE xmrzLogId = @xmrzLogId ";

                        helper.AddParameter("@xmrzApprovalStatus", SqlDbType.VarChar, "APPROVED");
                        helper.AddParameter("@xmrzApprovalDate", SqlDbType.DateTime, System.DateTime.Now.ToString());
                        helper.AddParameter("@xmrzApprovalBy", SqlDbType.Int, 1);    // TODO: 取用户id
                        helper.AddParameter("@xmrzLogId", SqlDbType.UniqueIdentifier, new Guid(xmrzLogId));    // TODO: 取用户id
                        helper.ExecuteNoneQuery();
                    }
                    else // 审核不通过
                    {
                        // 1、标记删除
                        helper.Command.CommandText = @"UPDATE [jiyuanXM] SET jyxmDeleteFlag = 1 WHERE jyxmId = @jyxmId ";
                        helper.AddParameter("@jyxmId", SqlDbType.Int, int.Parse(jyxmId));
                        retVal = helper.ExecuteNoneQuery();

                        // 2、记录日志
                        helper.Command.CommandText = @"UPDATE [jiyuanXMLog] SET xmrzApprovalStatus = @xmrzApprovalStatus, xmrzApprovalDate = @xmrzApprovalDate,
                                                   xmrzApprovalBy = @xmrzApprovalBy WHERE xmrzLogId = @xmrzLogId ";

                        helper.AddParameter("@xmrzApprovalStatus", SqlDbType.VarChar, "DISAPPROVED");
                        helper.AddParameter("@xmrzApprovalDate", SqlDbType.DateTime, System.DateTime.Now.ToString());
                        helper.AddParameter("@xmrzApprovalBy", SqlDbType.Int, 1);    // TODO: 取用户id
                        helper.AddParameter("@xmrzLogId", SqlDbType.UniqueIdentifier, new Guid(xmrzLogId));    // TODO: 取用户id
                        helper.ExecuteNoneQuery();
                    }

                    transaction.Commit();
                    return retVal;
                }
                catch
                {
                    transaction.Rollback();
                    throw;
                }
            }
        }

        protected override int doDeleteOperation()
        {
            using (SqlServerHelper helper = new SqlServerHelper())
            {
                helper.Open();
                SqlTransaction transaction = helper.Connection.BeginTransaction();
                helper.Command.Transaction = transaction;
                try
                {
                    int retVal = 0;
                    if (option) // 审核通过
                    {
                        // 1、删除数据（修改标志位，使数据无效）           
                        helper.Command.CommandText = @"UPDATE [jiyuanXM] SET jyxmDeleteFlag = 1 WHERE jyxmId = @jyxmId ";
                        helper.AddParameter("@jyxmId", SqlDbType.Int, int.Parse(jyxmId));
                        retVal = helper.ExecuteNoneQuery();

                        // 2、记录日志
                        helper.Command.CommandText = @"UPDATE [jiyuanXMLog] SET xmrzApprovalStatus = @xmrzApprovalStatus, 
                                                        xmrzApprovalDate = @xmrzApprovalDate,
                                                        xmrzApprovalBy = @xmrzApprovalBy WHERE xmrzLogId = @xmrzLogId ";

                        helper.AddParameter("@xmrzApprovalStatus", SqlDbType.VarChar, "APPROVED");
                        helper.AddParameter("@xmrzApprovalDate", SqlDbType.DateTime, System.DateTime.Now.ToString());
                        helper.AddParameter("@xmrzApprovalBy", SqlDbType.Int, 1);    // TODO: 取用户id
                        helper.AddParameter("@xmrzLogId", SqlDbType.UniqueIdentifier, new Guid(xmrzLogId));    // TODO: 取用户id
                        helper.ExecuteNoneQuery();
                    }
                    else // 审核不通过
                    {
                        helper.Command.CommandText = @"UPDATE [jiyuanXMLog] SET xmrzApprovalStatus = @xmrzApprovalStatus, 
                                                        xmrzApprovalDate = @xmrzApprovalDate,
                                                        xmrzApprovalBy = @xmrzApprovalBy WHERE xmrzLogId = @xmrzLogId ";

                        helper.AddParameter("@xmrzApprovalStatus", SqlDbType.VarChar, "DISAPPROVED");
                        helper.AddParameter("@xmrzApprovalDate", SqlDbType.DateTime, System.DateTime.Now.ToString());
                        helper.AddParameter("@xmrzApprovalBy", SqlDbType.Int, 1);    // TODO: 取用户id
                        helper.AddParameter("@xmrzLogId", SqlDbType.UniqueIdentifier, new Guid(xmrzLogId));    // TODO: 取用户id
                        helper.ExecuteNoneQuery();
                    }

                    transaction.Commit();
                    return retVal;
                }
                catch
                {
                    transaction.Rollback();
                    throw;
                }
            }
        }

        protected override int doUpdateOperation()
        {
            using (SqlServerHelper helper = new SqlServerHelper())
            {
                helper.Open();
                SqlTransaction transaction = helper.Connection.BeginTransaction();
                helper.Command.Transaction = transaction;
                try
                {
                    int retVal = 0;
                    if (option) // 审核通过
                    {
                        // 1、从日志中取得修改后的数据
                        string sqlText = string.Format("SELECT * FROM [jiyuanXMLog] WHERE xmrzLogId = '{0}'", xmrzLogId);
                        SqlDataReader newContent = SqlServerHelper.ExecuteReader(sqlText);
                        newContent.Read();

                        // 2、修改原有数据
                        helper.Command.CommandText = @"UPDATE [jiyuanXM] SET jyxmName = @jyxmName, jyxmProf = @jyxmProf WHERE jyxmId = @jyxmId ";
                        helper.AddParameter("@jyxmId", SqlDbType.Int, int.Parse(jyxmId));
                        helper.AddParameter("@jyxmName", SqlDbType.VarChar, newContent.GetSqlValue(newContent.GetOrdinal("xmrzName")));
                        helper.AddParameter("@jyxmProf", SqlDbType.Int, newContent.GetSqlValue(newContent.GetOrdinal("xmrzProf")));

                        retVal = helper.ExecuteNoneQuery();

                        // 3、记录日志
                        helper.Command.CommandText = @"UPDATE [jiyuanXMLog] SET xmrzApprovalStatus = @xmrzApprovalStatus, 
                                                        xmrzApprovalDate = @xmrzApprovalDate,
                                                        xmrzApprovalBy = @xmrzApprovalBy WHERE xmrzLogId = @xmrzLogId ";

                        helper.AddParameter("@xmrzApprovalStatus", SqlDbType.VarChar, "APPROVED");
                        helper.AddParameter("@xmrzApprovalDate", SqlDbType.DateTime, System.DateTime.Now.ToString());
                        helper.AddParameter("@xmrzApprovalBy", SqlDbType.Int, 1);    // TODO: 取用户id
                        helper.AddParameter("@xmrzLogId", SqlDbType.UniqueIdentifier, new Guid(xmrzLogId));    // TODO: 取用户id
                        helper.ExecuteNoneQuery();
                    }
                    else // 审核不通过
                    {
                        helper.Command.CommandText = @"UPDATE [jiyuanXMLog] SET xmrzApprovalStatus = @xmrzApprovalStatus, 
                                                        xmrzApprovalDate = @xmrzApprovalDate,
                                                        xmrzApprovalBy = @xmrzApprovalBy WHERE xmrzLogId = @xmrzLogId ";

                        helper.AddParameter("@xmrzApprovalStatus", SqlDbType.VarChar, "DISAPPROVED");
                        helper.AddParameter("@xmrzApprovalDate", SqlDbType.DateTime, System.DateTime.Now.ToString());
                        helper.AddParameter("@xmrzApprovalBy", SqlDbType.Int, 1);    // TODO: 取用户id
                        helper.AddParameter("@xmrzLogId", SqlDbType.UniqueIdentifier, new Guid(xmrzLogId));    // TODO: 取用户id
                        retVal = helper.ExecuteNoneQuery();
                    }

                    transaction.Commit();
                    return retVal;
                }
                catch
                {
                    transaction.Rollback();
                    throw;
                }
            }
        }

        public override SqlDataReader executeDataReader()
        {
            string sqlText = "dbcaac_GetPageOfRecords";

            string columns = @"jyxmId, jyxmName, CONVERT(VARCHAR(100), jyxmCreateDate, 23) AS jyxmCreateDate, jyxmNewFlag,
                                (SELECT nbyhName from [neibuYH] WHERE nbyhId = aTbl.jyxmCreateOwner) AS jyxmCreateOwner,
                                (SELECT jcyzyName from [jcyZY] WHERE jcyzyId = aTbl.jyxmProf) AS jyxmProf, 
                                (SELECT count(*) from [jiyuanXMLog] WHERE xmrzChangedId = aTbl.jyxmId 
                                    AND xmrzChangedType = 'UPDATE' AND xmrzApprovalStatus = 'UNAPPROVED') as xmUpdCount,
                                (SELECT count(*) from [jiyuanXMLog] WHERE xmrzChangedId = aTbl.jyxmId 
                                    AND xmrzChangedType = 'DELETE' AND xmrzApprovalStatus = 'UNAPPROVED') as xmDelCount";

            List<SqlParameter> sqlParams = new List<SqlParameter>();
            sqlParams.Add(new SqlParameter("@pageSize", pageSize));
            sqlParams.Add(new SqlParameter("@currentPage", pageNumber));
            sqlParams.Add(new SqlParameter("@columns", columns));
            sqlParams.Add(new SqlParameter("@tableName", "[jiyuanXM] AS aTbl"));
            sqlParams.Add(new SqlParameter("@condition", "jyxmDeleteFlag <> 1"));
            sqlParams.Add(new SqlParameter("@ascColumn", "jyxmId"));
            sqlParams.Add(new SqlParameter("@bitOrderType", 0));         // 0 是升序, 1 是降序
            sqlParams.Add(new SqlParameter("@pkColumn", "jyxmId"));

            return SqlServerHelper.ExecuteReaderProc(sqlText, sqlParams.ToArray());
        }
    }
}
